package com.hsnn.medstgmini.pro.project.dao.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.BidScoreRuleDao;
import com.hsnn.medstgmini.pro.project.model.BidScoreRule;
import com.hsnn.medstgmini.pro.project.model.BidScoreRuleLog;
import com.hsnn.medstgmini.pro.project.model.form.BidScoreRuleForm;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;
@Repository
public class BidScoreRuleDaoImpl implements BidScoreRuleDao {
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * 
	 *@category 获取项目企业资质的评标设置信息
	 *@author 邱磊
	 *@date 2015年9月8日 上午10:36:35
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getCompScoreSet(String projId){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.dic_ent_profile_id as dicEntProfileId,a.first_level_indi_code as firstLevelIndiCode,a.first_level_indi_name as firstLevelIndiName,")
		.append("b.score,b.bid_score_rule_id as isCheck, left(first_level_indi_code,9) as scoreGroup,b.bid_score_rule_id as bidScoreRuleId")
		.append(" from dic_ent_profile a")
		.append(" left join bid_score_rule b on a.dic_ent_profile_id=b.dic_ent_profile_id and b.score_object=1 and b.scor_type=1 and b.status=1")
		.append(" and b.proj_id=:projId")
		.append(" where a.status=1")
		.append(" order by left(a.first_level_indi_code,9),a.first_level_indi_code");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", projId);
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
	}
	/**
	 * 
	 *@category 获取项目药品资质的评标设置信息
	 *@author 邱磊
	 *@date 2015年9月8日 上午10:36:35
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getDrugScoreSet(String projId){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.dic_entprod_profile_id as dicEntprodProfileId,a.first_level_indi_code as firstLevelIndiCode,a.first_level_indi_name as firstLevelIndiName,")
		.append("b.score,b.bid_score_rule_id as isCheck, left(first_level_indi_code,9) as scoreGroup,b.bid_score_rule_id as bidScoreRuleId")
		.append(" from dic_entprod_profile a")
		.append(" left join bid_score_rule b on a.dic_entprod_profile_id=b.dic_entprod_profile_id and b.score_object=2 and b.scor_type=1 and b.status=1")
		.append(" and b.proj_id=:projId")
		.append(" where a.status=1")
		.append(" order by left(a.first_level_indi_code,9),a.first_level_indi_code");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", projId);
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
	}
	/**
	 * 
	 *@category 获取项目主观分的评标设置信息
	 *@author 邱磊
	 *@date 2015年9月8日 上午10:36:35
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getSubjectScoreSet(String projId){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.dic_subject_score_id as dicSubjectScoreId,a.subject_score_code as subjectScoreCode,a.subject_score_name as subjectScoreName,c.subject_score_name as fatherName")
		.append(",b.score,b.bid_score_rule_id as isCheck, c.subject_score_name as scoreGroup,b.bid_score_rule_id as bidScoreRuleId")
		.append(" from dic_subject_score a")
		.append(" left join dic_subject_score c on a.father_id=c.dic_subject_score_id")
		.append(" left join bid_score_rule b on a.dic_subject_score_id=b.dic_subject_score_id and b.score_object=3 and b.scor_type=2 and b.status=1")
		.append(" and b.proj_id=:projId")
		.append(" where a.status=1 and a.father_id is not null")
		.append(" order by a.father_id,a.subject_score_code");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", projId);
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
	}
	/**
	 * 
	 *@category 设置企业资质评分指标
	 *@author 邱磊
	 *@date 2015年9月8日 下午4:28:44
	 *@param page
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public void setCompScoreRule(Pagination page){
		List<BidScoreRuleForm> list = (List<BidScoreRuleForm>) page.getRows();
		if (null != list && list.size() > 0){
			for (int i = 0; i < list.size(); i++){
				BidScoreRuleForm form = list.get(i);
				if (1 == form.getChangeStatus()){//数据有修改
					if (1 == form.getCheckStatus()){//数据有选中
						if (StringUtils.isNotBlank(form.getBidScoreRuleId())){//有评分指标ID（数据要更新）
							String sql = "update bid_score_rule set score=:score,upd_datetime=SYSDATE(),upd_user=:userId where bid_score_rule_id=:bidScoreRuleId";
							Map<String, Object> params = new HashMap<String, Object>();
							params.put("score", form.getScore());
							params.put("userId", page.getConditions().get("userId"));
							params.put("bidScoreRuleId", form.getBidScoreRuleId());
							dao.executeSql(sql, params);
							String logsql = "INSERT into bid_score_rule_log select UUID(),a.* from bid_score_rule a where a.bid_score_rule_id=:bidScoreRuleId";
							params.remove("score");
							params.remove("userId");
							dao.executeSql(logsql, params);
						} else {
							BidScoreRule data = new BidScoreRule();
							data.setBidScoreRuleId(UUID.randomUUID().toString());
							data.setProjId(page.getConditions().get("projId"));
							data.setScoreObject(ParseNumber.toInteger(page.getConditions().get("scoreObject")));
							data.setDicEntProfileId(form.getDicEntProfileId());
							data.setDicEntprodProfileId(form.getDicEntprodProfileId());
							data.setDicSubjectScoreId(form.getDicSubjectScoreId());
							data.setScore(form.getScore());
							data.setScorType(ParseNumber.toInteger(page.getConditions().get("scorType")));
							data.setStatus(1);
							data.setUpdDatetime(new Date());
							data.setUpdUser(page.getConditions().get("userId"));
							dao.add(data);
							BidScoreRuleLog logdata = new BidScoreRuleLog();
							BeanUtils.copyProperties(data, logdata);
							logdata.setLogId(UUID.randomUUID().toString());
						}
					} else {
						if (StringUtils.isNotBlank(form.getBidScoreRuleId())){//有评分指标ID（数据要更新）
							String sql = "update bid_score_rule set status=:status,upd_datetime=SYSDATE(),upd_user=:userId where bid_score_rule_id=:bidScoreRuleId";
							Map<String, Object> params = new HashMap<String, Object>();
							params.put("status", 0);
							params.put("userId", page.getConditions().get("userId"));
							params.put("bidScoreRuleId", form.getBidScoreRuleId());
							dao.executeSql(sql, params);
							String logsql = "INSERT into bid_score_rule_log select UUID(),a.* from bid_score_rule a where a.bid_score_rule_id=:bidScoreRuleId";
							params.remove("score");
							params.remove("userId");
							params.remove("status");
							dao.executeSql(logsql, params);
						}
					}
				}
			}
		}
	}
	/**
	 * 
	 *@category 获取评分结果
	 *@author 邱磊
	 *@date 2015年9月8日 下午7:44:22
	 *@param page
	 *@return
	 */
	public Pagination getScreResultDataList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select bs.score_object as scoreObjectType,t.bid_projdrugs_score_id as bidProjdrugsScoreId,t.score_object as scoreObject,t.score_subject as scoreSubject,t.total_score as totalScore,")
		.append("a.proj_proddrug_id as projProddrugId,a.proj_id as projId,b.name,c.dic_name as projCat")
		.append(",d.drug_code as drugCode,e.qualityspec_name as qualityspecName,f.form_name as formName,g.spec_name as specName")
		.append(",d.factor,h.packunit_name as unit,i.comp_code as compCode,i.comp_name as compName,j.material_name as materialName")
		.append(" from bid_projdrugs_score t")
		.append(" left join bid_projproddrug a on t.bid_projdrugs_id=a.proj_proddrug_id")
		.append(" left join bid_project b on a.proj_id=b.proj_id")
		.append(" left join bid_score_rule bs on bs.proj_id=b.proj_id ")
		.append(" left join dic_sysdic c on c.dic_code=b.proj_cat and c.classify_code = :classifyCode")
		.append(" left join std_proddruginfo d on d.prod_id=a.prod_id")
		.append(" left join std_drugqualityspec e on e.qualityspec_id=d.qualityspec_id")
		.append(" left join dic_drugform f on f.form_id=e.form")
		.append(" left join dic_drugspec g on g.spec_id=e.spec")
		.append(" left join dic_packunit h on h.packunit_id=d.unit")
		.append(" left join std_compinfo i on i.comp_id=d.prod_comp_id")
		.append(" left join dic_drugmaterial j on j.material_id=d.material_id")
		.append(" where a.status=1 and b.status=1 and d.status=1 and e.status=1");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classifyCode", Constants.PROJ_CAT);
		Map<String, String> map = page.getConditions();
		if (StringUtils.isNotBlank(map.get("projId"))){
			sql.append(" and a.proj_id=:projId");
			params.put("projId", map.get("projId"));
		}
		if (StringUtils.isNotBlank(map.get("drugCode"))){
			sql.append(" and d.drug_code=:drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("qualityspecName"))){
			sql.append(" and (e.qualityspec_name like :qualityspecName or e.qualityspec_name_spel like :qualityspecNameSpel)");
			params.put("qualityspecName", "%"+map.get("drugCode").trim()+"%");
			params.put("qualityspecNameSpel", "%"+map.get("drugCode").trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(map.get("compCode"))){
			sql.append(" and i.comp_code=:compCode");
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and (i.comp_name like :compName or i.comp_spel_code like :compSpelCode)");
			params.put("compName", "%"+map.get("compName").trim()+"%");
			params.put("compSpelCode", "%"+map.get("compName").trim().toUpperCase()+"%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * 
	 *@category 获取得分详情
	 *@author 邱磊
	 *@date 2015年9月9日 上午10:21:54
	 *@return
	 */
	public Pagination getScoreResultDetail(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.score as getScore,")
		.append("case b.score_object when 1 then c.first_level_indi_name when 2 then d.first_level_indi_name when 3 then case b.scor_type when 2 then e.subject_score_name end end as detail,")
		.append("b.score_object")
		.append(" from bid_projdrugs_score_detail a")
		.append(" left join bid_score_rule b on b.bid_score_rule_id=a.bid_enc_score_rule_detail_id")
		.append(" left join dic_ent_profile c on b.dic_ent_profile_id=c.dic_ent_profile_id")
		.append(" left join dic_entprod_profile d on d.dic_entprod_profile_id=b.dic_entprod_profile_id")
		.append(" left join dic_subject_score e on e.dic_subject_score_id=b.dic_subject_score_id")
		.append(" where a.bid_projdrugs_score_id=:bidProjdrugsScoreId")
		.append(" order by b.score_object");
		Map<String, Object>  params = new HashMap<String, Object>();
		params.put("bidProjdrugsScoreId", page.getConditions().get("bidProjdrugsScoreId"));
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * 
	 *@category 获取报价解密数据列表
	 *@author 邱磊
	 *@date 2015年9月9日 下午1:20:28
	 *@return
	 */
	public Pagination getViewDrugPriceList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select b.proj_id projId,b.name,d.dic_name as projCat,c.drug_code as drugCode,e.qualityspec_name as qualityspecName,f.form_name as formName,")
		.append("g.spec_name as specName,c.factor,h.packunit_name as unit,i.comp_code as compCode,i.comp_name as compName,j.material_name as materialName")
		.append(",a.decrypt_status as decryptStatus,a.decrypt_price as decryptPrice,DATE_FORMAT(a.decrypt_datetime,'%Y-%m-%d %H:%i:%S') as decryptDatetime")
		.append(" from bid_projproddrug a")
		.append(" left join bid_project b on a.proj_id=b.proj_id")
		.append(" left join std_proddruginfo c on c.prod_id=a.prod_id")
		.append(" left join dic_sysdic d on d.dic_code=b.proj_cat and d.classify_code=:classifyCode")
		.append(" left join std_drugqualityspec e on e.qualityspec_id=c.qualityspec_id")
		.append(" left join dic_drugform f on f.form_id=e.form")
		.append(" left join dic_drugspec g on g.spec_id=e.spec")
		.append(" left join dic_packunit h on h.packunit_id=c.unit")
		.append(" left join std_compinfo i on i.comp_id=c.prod_comp_id")
		.append(" left join dic_drugmaterial j on j.material_id=c.material_id")
		.append(" where a.status=1 and b.status=1 and c.status=1 and e.status=1 and i.status=1");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classifyCode", Constants.PROJ_CAT);
		Map<String, String> map = page.getConditions();
		if (StringUtils.isNotBlank(map.get("projId"))){
			sql.append(" and a.proj_id=:projId");
			params.put("projId", map.get("projId"));
		}
		if (StringUtils.isNotBlank(map.get("projCat"))){
			sql.append(" and b.proj_cat=:projCat");
			params.put("projCat", map.get("projCat"));
		}
		if (StringUtils.isNotBlank(map.get("decryptPrice"))){
			sql.append(" and a.decrypt_price=:decryptPrice");
			params.put("decryptPrice", map.get("decryptPrice"));
		}
		if (StringUtils.isNotBlank(map.get("drugCode"))){
			sql.append(" and c.drug_code=:drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("qualityspecName"))){
			sql.append(" and (e.qualityspec_name like :qualityspecName or e.qualityspec_name_spel like :qualityspecNameSpel)");
			params.put("qualityspecName", "%"+map.get("qualityspecName").trim()+"%");
			params.put("qualityspecNameSpel", "%"+map.get("qualityspecName").trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(map.get("compCode"))){
			sql.append(" and i.comp_code=:compCode");
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and (i.comp_name like :compName or i.comp_spel_code like :compSpelCode)");
			params.put("compName", "%"+map.get("compName").trim()+"%");
			params.put("compSpelCode", "%"+map.get("compName").trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(map.get("isBusScope"))){
			sql.append(" and a.is_bus_scope=:isBusScope");
			params.put("isBusScope", map.get("isBusScope"));
		}
		if (StringUtils.isNotBlank(map.get("isSemiBidding"))){
			sql.append(" and a.is_semi_bidding=:isSemiBidding");
			params.put("isSemiBidding", map.get("isSemiBidding"));
		}
		if (StringUtils.isNotBlank(map.get("isBidding"))){
			sql.append(" and a.is_bidding=:isBidding");
			params.put("isBidding", map.get("isBidding"));
		}
		if (StringUtils.isNotBlank(map.get("decryptStatus"))){
			sql.append(" and a.decrypt_status=:decryptStatus");
			params.put("decryptStatus", map.get("decryptStatus"));
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 解密企业
	 *@author wangbing
	 *@date 2015年9月9日 上午10:21:54
	 *@return
	 */
	@Override
	public Pagination getDecryptCompList(Pagination page){
		StringBuffer sql = new StringBuffer()
			.append("	SELECT sc.comp_name compName,sc.comp_id compId, 	")
			.append("   (SELECT COUNT(*) FROM bid_projprodcompany b WHERE b.proj_id=bpc.proj_id) compCount,	")
			.append("   (SELECT COUNT(*) FROM bid_projproddrug c LEFT JOIN  std_proddruginfo d ON d.prod_id=c.prod_id	")
			.append("    WHERE c.proj_id=bpc.proj_id AND d.prod_comp_id = sc.comp_id AND (c.audit_status=1 OR(c.audit_status=2 AND c.complain_status=1))) drugCount,	")
			.append("   (SELECT COUNT(*) FROM bid_projproddrug c LEFT JOIN  std_proddruginfo d ON d.prod_id=c.prod_id	")
			.append("   WHERE c.proj_id=bpc.proj_id AND d.prod_comp_id = sc.comp_id AND (c.audit_status=1 OR(c.audit_status=2 AND c.complain_status=1)) AND c.decrypt_status=2) drugDecryptCount 	")
			.append("	FROM bid_projprodcompany bpc LEFT JOIN std_compinfo sc ON sc.comp_id = bpc.prod_comp_id	")
			.append("	WHERE bpc.proj_id = :projId AND								")
			.append("	EXISTS (SELECT bp.* FROM bid_projproddrug bp 				")
			.append("	LEFT JOIN std_proddruginfo sp ON sp.prod_id=bp.prod_id		")
			.append("	WHERE bp.proj_id = bpc.proj_id AND bp.decrypt_status = 2	")
			.append("	AND sp.prod_comp_id = sc.comp_id)							");
		Map<String, Object>  params = new HashMap<String, Object>();
		params.put("projId", page.getConditions().get("projId"));
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 解密药品
	 *@author wangbing
	 *@date 2015年9月9日 上午10:21:54
	 *@return
	 */
	@Override
	public Pagination getDecryptDrugList(Pagination page){
		StringBuffer sql = new StringBuffer()
			.append("	SELECT sd.qualityspec_name drugName,dds.spec_name spec,ddf.form_name form,")
			.append("   (SELECT COUNT(*) FROM bid_projproddrug b WHERE  b.decrypt_status=2 and b.proj_id=:projId) drugDecryptCount,")
			.append("	(SELECT COUNT(*) FROM bid_projproddrug a WHERE (a.decrypt_status=1 OR a.decrypt_status=2) and a.proj_id=:projId) drugCount")
			.append("	FROM bid_projproddrug bp ")
			.append("	LEFT JOIN std_proddruginfo sp ON sp.prod_id = bp.prod_id")
			.append("	LEFT JOIN  std_drugqualityspec sd ON sd.qualityspec_id = sp.qualityspec_id")
			.append("	LEFT JOIN dic_drugspec dds ON dds.spec_id = sd.spec")
			.append("	LEFT JOIN dic_drugform ddf ON ddf.form_id = sd.form")
			.append("	WHERE bp.decrypt_status = 2 AND proj_id =:projId ");
		Map<String, Object>  params = new HashMap<String, Object>();
		params.put("projId", page.getConditions().get("projId"));
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return page;
	}
}
